# -*- coding: utf8 -*-

from project.mysql.table import Table

class TableFile( Table ):

    _table_name = "file"

    def create( self ):
        cur = self.getCursor()

        # Create table
        cur.execute(
            "CREATE TABLE IF NOT EXISTS `%s` (" % self._table_name+
            "id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, "+
            "parent_id INTEGER UNSIGNED NOT NULL, "+
            "hash_id INTEGER UNSIGNED NOT NULL, "+
            "device INTEGER UNSIGNED NOT NULL, "+
            "size INTEGER UNSIGNED NOT NULL, "+
            "mtime INTEGER UNSIGNED NOT NULL, "+
            "mime VARCHAR(255) NOT NULL, "+
            "name VARCHAR(255) NOT NULL ) ENGINE %s" % self.getEngine()
        )
        try:
            cur.execute(
                "ALTER TABLE `%s` ADD INDEX `hash` (`hash_id`)" % self._table_name
            )
            cur.execute(
                "ALTER TABLE `%s` ADD INDEX `mime` (`mime`)" % self._table_name
            )
        except:
            pass
        cur.close()
        return


    def insert( self, parent_id, device, mime, size, mtime, name ):
        cur = self.getCursor()

        rows = cur.execute(
            "INSERT INTO `%s` (" % self._table_name+
            "parent_id, hash_id, device, mime, size, mtime, name"+
            ") VALUES ("+
            "%(parent_id)s, %(hash_id)s, %(device)s, %(mime)s, %(size)s, %(mtime)s, %(name)s"+
            ")", {
                "parent_id":parent_id,
                "hash_id":0,
                "device":device,
                "mime":mime,
                "size":size,
                "mtime":mtime,
                "name":name
            }
        )
        self.getConnection().commit()

        file_id = None

        if rows:
            file_id = self.getConnection().insert_id()

        cur.close()
        return file_id


    def link_hash( self, file_id, hash_id ):
        cur = self.getCursor()

        rc = cur.execute("UPDATE `%s` " % self._table_name +
                         "SET hash_id=%(hash)s WHERE id=%(file)s", {
            "hash": hash_id,
            "file": file_id
        })

        self.getConnection().commit()

        cur.close()
        return rc


    def get_file_hash_id( self, file_id ):
        cur = self.getCursor()
        cur.execute("SELECT hash_id FROM `%s` " % self._table_name +
                    "WHERE id=%(id)s", {"id": file_id})
        item = cur.fetchone()
        cur.close()
        if item:
            return item["hash_id"]
        return False


    def get_nonuniq_hash_ids(self):
        cur = self.getCursor()

        hash_ids = ()

        cur.execute("SELECT hash_id FROM `%s` WHERE hash_id IS NOT NULL GROUP BY hash_id HAVING COUNT(id)>1" % self._table_name)
        for item in cur.fetchall():
            hash_ids += (str(item["hash_id"]),)

        cur.close()
        return hash_ids


    def get_file2hash_by_hash_ids( self, hash_ids ):
        cur = self.getCursor()

        file_hash = {}
        if len(hash_ids):
            cur.execute("SELECT id,hash_id FROM `%s` WHERE hash_id IN (" % self._table_name + ",".join(hash_ids) + ")" )
            for item in cur.fetchall():
                file_hash[ str(item["id"]) ] = str(item["hash_id"])

        cur.close()
        return file_hash


    def get_by_ids( self, file_ids ):
        cur = self.getCursor()

        files = []
        if len(file_ids):
            cur.execute("SELECT * FROM `%s` WHERE id IN (" % self._table_name + ",".join(file_ids) + ")" )
            files = cur.fetchall()

        cur.close()
        return files


    def get_count_by_mimes( self, mimes ):
        cur = self.getCursor()

        files = None
        count = 0
        if len(mimes):
            cur.execute("SELECT COUNT(1) as c FROM `%s` WHERE mime IN ('" % self._table_name + "','".join(mimes) + "')" )
            files = cur.fetchone()
        if files:
            count = int(files['c'])

        cur.close()
        return count


    def get_by_mimes( self, mimes, offset=0, limit=100 ):
        cur = self.getCursor()

        files = []
        if len(mimes):
            cur.execute("SELECT * FROM `%s` WHERE mime IN ('" % self._table_name
                        + "','".join(mimes) + "')"
                        + " LIMIT %d OFFSET %d" % (limit, offset,) )
            files = cur.fetchall()

        cur.close()
        return files


    def get( self, file_id ):
        cur = self.getCursor()

        cur.execute("SELECT * FROM `%s` " % self._table_name +
                    "WHERE id=%(id)s", {"id":file_id} )
        item = cur.fetchone()

        cur.close()
        return item


    def remove( self, file_id ):
        cur = self.getCursor()
        rc = cur.execute("DELETE FROM %s " % self._table_name +
                         "WHERE id=%(id)s",
                {"id":file_id}
        )
        self.getConnection().commit()
        cur.close()
        return rc

    pass
